package com.homework.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.homework.bean.CourseBean;
import com.homework.bean.CourseListBean;
import com.homework.bean.PageInfoBean;
import com.homework.bean.UserBean;
import com.homework.bean.UserCourseBean;
import com.homework.bean.UserCourseListBean;
import com.homework.service.PageInfoService;
import com.homework.util.Constants;
import com.homework.util.connDB;

public class CourseDao {

	private static CourseDao courseDao = null;
	private connDB conn = new connDB();
	private ResultSet rs;
	private PageInfoService pageInfoService;
	// sql语句
	private String sql = "";

	public CourseDao() {
		pageInfoService = new PageInfoService();
	}

	public static CourseDao getInstance() {
		if (courseDao == null) {
			courseDao = new CourseDao();
		}
		return courseDao;
	}

	// 班级列表
	public CourseListBean classShow(UserBean userBean, PageInfoBean pageInfoBean)
			throws SQLException {

		// 数据库连接
		if (userBean.getRoleId().equals("3")) {
			sql = "select * from t_course;";
		} else {
			sql = "select t_courseTemp.* from (select t_course.* from t_course where f_courseStatus = 1) as t_courseTemp natural join (select f_courseId from t_choose where f_chooseStatus = 1 and f_userId = "
					+ userBean.getUserId() + ") as t_chooseTemp;";

		}
		rs = conn.executeQuery(sql);

		// --------------分页基本参数获取---------------
		pageInfoBean.setRs(rs);
		pageInfoService.setPageInfoBean(pageInfoBean);

		// 获取courseListBean
		CourseListBean courseListBean = new CourseListBean();
		for (int i = 0; i < pageInfoBean.getNum(); i++) {
			CourseBean courseBean = new CourseBean();
			courseBean.setCourseId(rs.getString("f_courseId"));
			courseBean.setCourseName(rs.getString("f_courseName"));
			courseBean.setCredit(rs.getString("f_credit"));
			courseBean.setSchoolYear(rs.getString("f_schoolYear"));
			courseBean.setTerm(rs.getString("f_term"));
			courseBean.setCourseStart(rs.getString("f_courseStart"));
			courseBean.setCourseEnd(rs.getString("f_courseEnd"));
			courseBean.setCourseStatus(rs.getString("f_courseStatus"));
			courseListBean.getCourseList().add(courseBean);
			rs.next(); // 下一条记录
		}

		rs.close();
		conn.close();
		return courseListBean;
	}

	// 班级查询
	public CourseListBean classQuery(UserBean userBean, CourseBean courseBean,
			PageInfoBean pageInfoBean) throws SQLException {
		// TODO Auto-generated method stub
		// 数据库连接
		if (userBean.getRoleId().equals("3")) {
			sql = "select * from  t_course where f_courseName like '%"
					+ courseBean.getCourseName() + "%';";
		} else {
			sql = "select * from (select t_course.* from t_course natural join(select f_courseId from t_choose where f_chooseStatus = 1 and f_userId = '"
					+ userBean.getUserId()
					+ "') as t_chooseTemp) as t_courseTemp where f_courseName like '%"
					+ courseBean.getCourseName() + "%' and f_courseStatus = 1;";
		}
		rs = conn.executeQuery(sql);

		// --------------分页基本参数获取---------------
		pageInfoBean.setRs(rs);
		pageInfoService.setPageInfoBean(pageInfoBean);

		// 获取courseListBean
		CourseListBean courseListBean = new CourseListBean();
		for (int i = 0; i < pageInfoBean.getNum(); i++) {
			CourseBean courseBean_temp = new CourseBean();
			courseBean_temp.setCourseId(rs.getString("f_courseId"));
			courseBean_temp.setCourseName(rs.getString("f_courseName"));
			courseBean_temp.setCredit(rs.getString("f_credit"));
			courseBean_temp.setSchoolYear(rs.getString("f_schoolYear"));
			courseBean_temp.setTerm(rs.getString("f_term"));
			courseBean_temp.setCourseStart(rs.getString("f_courseStart"));
			courseBean_temp.setCourseEnd(rs.getString("f_courseEnd"));
			courseBean_temp.setCourseStatus(rs.getString("f_courseStatus"));
			courseListBean.getCourseList().add(courseBean_temp);
			rs.next(); // 下一条记录
		}

		rs.close();
		conn.close();
		return courseListBean;
	}

	// 新增班级
	public void classAdd(CourseBean courseBean, UserBean userBean)
			throws SQLException {
		// 数据库连接
		sql = "insert into t_course (f_courseName,f_credit,f_schoolYear,f_term,f_courseStart,f_courseEnd,f_courseStatus) values ('"
				+ courseBean.getCourseName()
				+ "',"
				+ courseBean.getCredit()
				+ ",'"
				+ courseBean.getSchoolYear()
				+ "','"
				+ courseBean.getTerm()
				+ "',"
				+ courseBean.getCourseStart()
				+ "," + courseBean.getCourseEnd() + ",1);";
		conn.executeUpdate(sql);
		sql = "select * from t_course;";
		rs = conn.executeQuery(sql);
		rs.last();// 光标在最后一行
		int i = rs.getRow();// 获得当前行号 ，及总记录数
		sql = "insert into t_choose (f_courseId,f_userId,f_chooseStatus) values (" + i + ","
				+ userBean.getUserId() + ",1);";
		conn.executeUpdate(sql);
		rs.close();
		conn.close();
	}

	// 删除班级_教师
	public void classDelete_teacher(CourseBean courseBean) throws SQLException {
		List<Integer> list = new ArrayList<Integer>();
		// 删除相似度
		sql = "select distinct s.f_similarityId from t_similarity s,(select t_homeworkTemp1.f_homeworkId from (select t_homework.f_homeworkId,t_homework.f_homeworkStatus from t_homework natural join (select f_chooseId from t_choose where f_courseId = "
				+ courseBean.getCourseId()
				+ " and f_chooseStatus = 1) as t_chooseTemp) as t_homeworkTemp1 where f_homeworkStatus <> 5) as t_homeworkTemp where t_homeworkTemp.f_homeworkId = s.f_homeworkId or t_homeworkTemp.f_homeworkId = s.f_homeworkIdB;";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_similarityId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_similarity set f_similarityStatus = 2 where f_similarityId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 删除作业
		sql = "select t_homework.f_homeworkId from t_homework natural join (select f_chooseId from t_choose where f_chooseStatus = 1 and f_courseId = "
				+ courseBean.getCourseId() + ") as t_chooseTemp;";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_homeworkId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_homework set f_homeworkStatus = 5 where f_homeworkId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 删除通知
		sql = "select t_inform.f_informId from t_inform natural join (select f_chooseId from t_choose where f_chooseStatus = 1 and f_courseId = "
				+ courseBean.getCourseId() + ") as t_chooseTemp;";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_informId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_inform set f_informStatus = 2 where f_informId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 删除回复
		sql = "select f_chooseId from t_choose where f_courseId = "
				+ courseBean.getCourseId() + ";";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_chooseId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_answer set f_answerStatus = 2 where f_chooseId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 删除问题
		sql = "select t_question.f_questionId from t_question natural join (select f_chooseId from t_choose where f_chooseStatus = 1 and f_courseId = "
				+ courseBean.getCourseId() + ") as t_chooseTemp;";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_questionId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_question set f_questionStatus = 3 where f_questionId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 删除学习资源
		sql = "select t_resource.f_resourceId from t_resource natural join (select f_chooseId from t_choose where f_chooseStatus = 1 and f_courseId = "
				+ courseBean.getCourseId() + ") as t_chooseTemp;";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_resourceId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_resource set f_resourceStatus = 2 where f_resourceId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 删除选课关系
		sql = "update t_choose set f_chooseStatus = 2 where f_courseId = "
				+ courseBean.getCourseId() + ";";
		conn.executeUpdate(sql);
		// 删除课程
		sql = "update t_course set f_courseStatus = 2 where f_courseId = "
				+ courseBean.getCourseId() + ";";
		conn.executeUpdate(sql);
		rs.close();
		conn.close();
	}

	// 删除班级_学生
	public void classDelete_student(CourseBean courseBean, UserBean userBean)
			throws SQLException {
		// 删除某学生这门选课的作业的相似度
		sql = "update t_similarity set f_similarityStatus = 2 where f_homeworkId in (select f_homeworkId from t_homework where f_chooseId = (select f_chooseId from t_choose where f_userId = "
				+ userBean.getUserId()
				+ " and f_courseId = "
				+ courseBean.getCourseId() + "));";
		conn.executeUpdate(sql);
		// 删除某学生这门选课的作业
		sql = "update t_homework set f_homeworkStatus = 5 where f_chooseId = (select f_chooseId from t_choose where f_userId = "
				+ userBean.getUserId()
				+ " and f_courseId = "
				+ courseBean.getCourseId() + ") ";
		conn.executeUpdate(sql);
		// 删除某学生这门选课的学习资源
		sql = "update t_resource set f_resourceStatus = 2 where f_chooseId = (select f_chooseId from t_choose where f_userId = "
				+ userBean.getUserId()
				+ " and f_courseId = "
				+ courseBean.getCourseId() + ") ";
		conn.executeUpdate(sql);
		// 删除某学生这门选课的通知
		sql = "update t_inform set f_informStatus = 2 where f_chooseId = (select f_chooseId from t_choose where f_userId = "
				+ userBean.getUserId()
				+ " and f_courseId = "
				+ courseBean.getCourseId() + ") ";
		conn.executeUpdate(sql);
		// 删除某学生这门选课的问题
		sql = "update t_question set f_questionStatus = 3 where f_chooseId = (select f_chooseId from t_choose where f_userId = "
				+ userBean.getUserId()
				+ " and f_courseId = "
				+ courseBean.getCourseId() + ") ";
		conn.executeUpdate(sql);
		// 删除某学生的选课关系
		sql = "update t_choose set f_chooseStatus = 2 where f_courseId = "
				+ courseBean.getCourseId() + " and f_userId = "
				+ userBean.getUserId() + ";";
		conn.executeUpdate(sql);
		conn.close();
	}

	// 班级恢复_老师删除的班级
	public void classRecovery_teacher(CourseBean courseBean)
			throws NumberFormatException, SQLException {
		List<Integer> list = new ArrayList<Integer>();
		// 恢复相似度
		sql = "select distinct s.f_similarityId from t_similarity s,(select t_homeworkTemp1.f_homeworkId from (select t_homework.f_homeworkId,t_homework.f_homeworkStatus from t_homework natural join (select f_chooseId from t_choose where f_courseId = "
				+ courseBean.getCourseId()
				+ " and f_chooseStatus = 1) as t_chooseTemp) as t_homeworkTemp1 where f_homeworkStatus <> 5) as t_homeworkTemp where t_homeworkTemp.f_homeworkId = s.f_homeworkId or t_homeworkTemp.f_homeworkId = s.f_homeworkIdB;";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_similarityId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_similarity set f_similarityStatus = 1 where f_similarityId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 恢复作业
		sql = "select t_homework.f_homeworkId from t_homework natural join (select f_chooseId from t_choose where f_chooseStatus = 1 and f_courseId = "
				+ courseBean.getCourseId() + ") as t_chooseTemp;";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_homeworkId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_homework set f_homeworkStatus = 1 where f_homeworkId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 恢复通知
		sql = "select t_inform.f_informId from t_inform natural join (select f_chooseId from t_choose where f_chooseStatus = 1 and f_courseId = "
				+ courseBean.getCourseId() + ") as t_chooseTemp;";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_informId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_inform set f_informStatus = 1 where f_informId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 恢复回复
		sql = "select f_chooseId from t_choose where f_courseId = "
				+ courseBean.getCourseId() + ";";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_chooseId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_answer set f_answerStatus = 1 where f_chooseId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 恢复问题
		sql = "select t_question.f_questionId from t_question natural join (select f_chooseId from t_choose where f_chooseStatus = 1 and f_courseId = "
				+ courseBean.getCourseId() + ") as t_chooseTemp;";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_questionId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_question set f_questionStatus = 1 where f_questionId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 恢复学习资源
		sql = "select t_resource.f_resourceId from t_resource natural join (select f_chooseId from t_choose where f_chooseStatus = 1 and f_courseId = "
				+ courseBean.getCourseId() + ") as t_chooseTemp;";
		rs = conn.executeQuery(sql);
		if (rs.next()) {
			list.add(Integer.parseInt(rs.getString("f_resourceId")));
		}
		for (int i = 0; i < list.size(); i++) {
			sql = "update t_resource set f_resourceStatus = 1 where f_resourceId = "
					+ list.get(i) + "";
			conn.executeUpdate(sql);
		}
		list.clear();
		// 恢复选课关系
		sql = "update t_choose set f_chooseStatus = 1 where f_courseId = "
				+ courseBean.getCourseId() + ";";
		conn.executeUpdate(sql);
		conn.close();
		// 恢复课程
		sql = "update t_course set f_courseStatus = 1 where f_courseId = "
				+ courseBean.getCourseId() + ";";
		conn.executeUpdate(sql);
		rs.close();
		conn.close();
	}

	// 选课列表
	public UserCourseListBean classChooseShow(UserBean userBean,
			PageInfoBean pageInfoBean) throws SQLException {
		// 数据库连接
		sql = "select * from (select * from t_choose natural join (select * from t_user where f_roleId = 2 and f_userStatus <> 5) as t_userTemp) as t_temp natural join (select * from t_course where f_courseStatus = 1) as t_courseTemp;";
		rs = conn.executeQuery(sql);

		// --------------分页基本参数获取---------------
		pageInfoBean.setRs(rs);
		pageInfoService.setPageInfoBean(pageInfoBean);

		// 获取userCourseListBean
		UserCourseListBean userCourseListBean = new UserCourseListBean();
		for (int i = 0; i < pageInfoBean.getNum(); i++) {
			UserCourseBean userCourseBean = new UserCourseBean();
			CourseBean courseBean = new CourseBean();
			UserBean userBeanTemp = new UserBean();
			courseBean.setCourseId(rs.getString("f_courseId"));
			courseBean.setCourseName(rs.getString("f_courseName"));
			courseBean.setCredit(rs.getString("f_credit"));
			courseBean.setSchoolYear(rs.getString("f_schoolYear"));
			courseBean.setTerm(rs.getString("f_term"));
			courseBean.setCourseStart(rs.getString("f_courseStart"));
			courseBean.setCourseEnd(rs.getString("f_courseEnd"));
			courseBean.setCourseStatus(rs.getString("f_courseStatus"));
			userBeanTemp.setUserId(rs.getString("f_userId"));
			userBeanTemp.setUsername(rs.getString("f_username"));
			userBeanTemp.setRealname(rs.getString("f_realname"));
			userBeanTemp.setUserStatus(rs.getString("f_userStatus"));
			userBeanTemp.setPhone(rs.getString("f_phone"));
			userBeanTemp.setEmail(rs.getString("f_email"));
			userBeanTemp.setLasttime(rs.getString("f_lasttime"));
			userBeanTemp.setLoginip(rs.getString("f_loginip"));
			userBeanTemp.setCreatonTime(rs.getString("f_creationTime"));
			userBeanTemp.setRoleId(rs.getString("f_roleId"));
			userBeanTemp.setUniversity(rs.getString("f_university"));
			userBeanTemp.setInstitute(rs.getString("f_institute"));
			userBeanTemp.setGrade(rs.getString("f_grade"));
			userBeanTemp.setMajor(rs.getString("f_major"));
			userBeanTemp.setClasses(rs.getString("f_classes"));
			userCourseBean.setCourseBean(courseBean);
			userCourseBean.setUserBean(userBeanTemp);
			userCourseListBean.getUserCourseList().add(userCourseBean);
			rs.next(); // 下一条记录
		}

		rs.close();
		conn.close();
		return userCourseListBean;
	}

	// 选课列表_查询
	public UserCourseListBean classChooseQuery(UserBean userBean,
			PageInfoBean pageInfoBean, CourseBean courseBean)
			throws SQLException {
		// 数据库连接
		sql = "select * from (select * from t_choose natural join (select * from t_user where f_roleId = 2 and f_userStatus <> 5) as t_userTemp) as t_temp natural join (select * from t_course where f_courseStatus = 1 and f_courseName like '%"
				+ courseBean.getCourseName() + "%') as t_courseTemp;";
		rs = conn.executeQuery(sql);

		// --------------分页基本参数获取---------------
		pageInfoBean.setRs(rs);
		pageInfoService.setPageInfoBean(pageInfoBean);

		// 获取userCourseListBean
		UserCourseListBean userCourseListBean = new UserCourseListBean();
		for (int i = 0; i < pageInfoBean.getNum(); i++) {
			UserCourseBean userCourseBean = new UserCourseBean();
			CourseBean courseBeanTemp = new CourseBean();
			UserBean userBeanTemp = new UserBean();
			courseBeanTemp.setCourseId(rs.getString("f_courseId"));
			courseBeanTemp.setCourseName(rs.getString("f_courseName"));
			courseBeanTemp.setCredit(rs.getString("f_credit"));
			courseBeanTemp.setSchoolYear(rs.getString("f_schoolYear"));
			courseBeanTemp.setTerm(rs.getString("f_term"));
			courseBeanTemp.setCourseStart(rs.getString("f_courseStart"));
			courseBeanTemp.setCourseEnd(rs.getString("f_courseEnd"));
			courseBeanTemp.setCourseStatus(rs.getString("f_courseStatus"));
			userBeanTemp.setUserId(rs.getString("f_userId"));
			userBeanTemp.setUsername(rs.getString("f_username"));
			userBeanTemp.setRealname(rs.getString("f_realname"));
			userBeanTemp.setUserStatus(rs.getString("f_userStatus"));
			userBeanTemp.setPhone(rs.getString("f_phone"));
			userBeanTemp.setEmail(rs.getString("f_email"));
			userBeanTemp.setLasttime(rs.getString("f_lasttime"));
			userBeanTemp.setLoginip(rs.getString("f_loginip"));
			userBeanTemp.setCreatonTime(rs.getString("f_creationTime"));
			userBeanTemp.setRoleId(rs.getString("f_roleId"));
			userBeanTemp.setUniversity(rs.getString("f_university"));
			userBeanTemp.setInstitute(rs.getString("f_institute"));
			userBeanTemp.setGrade(rs.getString("f_grade"));
			userBeanTemp.setMajor(rs.getString("f_major"));
			userBeanTemp.setClasses(rs.getString("f_classes"));
			userCourseBean.setCourseBean(courseBeanTemp);
			userCourseBean.setUserBean(userBeanTemp);
			userCourseListBean.getUserCourseList().add(userCourseBean);
			rs.next(); // 下一条记录
		}

		rs.close();
		conn.close();
		return userCourseListBean;
	}

	// 提交选课
	public int classChoose(UserBean userBean, List<Integer> courseIdList)
			throws SQLException {
		int userId = Integer.parseInt(userBean.getUserId());
		int i = 0;
		int result = 0;
		for (i = 0; i < courseIdList.size(); i++) {
			// 判断是否存在选课
			if (isClassChoose(courseIdList.get(i), userId)) {// 存在选课
				// 跳出循环，恢复之前所有操作
				break;
			} else {
				// 提交选课
				sql = "insert into t_choose (f_courseId,f_userId,f_chooseStatus) values ("
						+ courseIdList.get(i) + "," + userId + ",1);";
				conn.executeUpdate(sql);
			}
		}
		if (i < courseIdList.size()) {// 恢复之前所有操作,提示“选课失败”
			for (int j = 0; j < i; j++) {
				sql = "delete from t_choose where f_userId = " + userId
						+ " and f_courseId = " + courseIdList.get(j) + ";";
				conn.executeUpdate(sql);
			}
			result = Constants.RESULT_classChooseFail;
			conn.close();
			return result;
		} else {// 提示选课成功
			result = Constants.RESULT_classChooseSuccess;
			conn.close();
			return result;
		}

	}

	// 判断是否存在选课
	public boolean isClassChoose(int courseId, int userId) throws SQLException {
		sql = "select * from t_choose where f_courseId = " + courseId
				+ " and f_userId = " + userId + ";";
		rs = conn.executeQuery(sql);
		if (rs.next()) {// 已存在选课
			rs.close();
			conn.close();
			return true;
		} else {// 不存在选课
			rs.close();
			conn.close();
			return false;
		}
	}

}
